package com.sylli.oeuf.server.db.sql;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.sylli.oeuf.server.db.AbstractProvider;
import com.sylli.oeuf.server.exception.BasedException;
import com.sylli.oeuf.server.exception.ExceptionCause;
import com.sylli.oeuf.server.object.transferable.BasedTO;

public abstract class MySQLBasedProvider<T extends BasedTO> extends AbstractProvider<T> {
	
	public MySQLBasedProvider() {
		
	}
	
//	public MySQLBasedProvider(DatabaseCache<T> cache) {
//		super(cache);
//	}

	protected static Logger log = LoggerFactory.getLogger( "db.provider" );
	
	protected MySQLConnector mysqlConnector = MySQLConnector.getInstance();
	
	protected String interactTableName = "unset";
	
	// abstraction
	
	protected abstract T convertResultSetToObject(ResultSet rs) throws BasedException;
	
	protected abstract PreparedStatement convertObjectToUpdateQuery(T obj) throws BasedException;
	
	protected abstract PreparedStatement convertObjectToInsertQuery(T obj) throws BasedException;
	
	/* batching (unused) */
	protected abstract PreparedStatement convertObjectToInsertQuery(Collection<T> objs) throws BasedException;
	
	protected abstract Collection<T> convertResultSetToObjects(ResultSet rs) throws BasedException;
	
	// add some generally used functions here.
	
	@Override
	public void delete(String where_clause) throws BasedException {
		String _sql = "DELETE FROM " + interactTableName + " WHERE " + where_clause + ";";
		try {
			Statement st = mysqlConnector.getConnectionObject().createStatement();
			int r = st.executeUpdate(_sql);
			st.close();
			log.debug("Execute: " + _sql + " returns " + r);
		} catch (SQLException e) {
			throw new BasedException(ExceptionCause.DatabaseExecuteFail, e);
		}
	}

	@Override
	public boolean exists(String where_clause) throws BasedException {
		String _sql = "SELECT COUNT(*) FROM " + interactTableName + " WHERE " + where_clause + ";";
		try {
			Statement st = mysqlConnector.getConnectionObject().createStatement();
			ResultSet rs = st.executeQuery(_sql);
			rs.first();
			int result = rs.getInt(1);
			st.close();
			rs.close();
			return (result > 0);
		} catch (SQLException e) {
			throw new BasedException(ExceptionCause.DatabaseExecuteFail, e);
		}
	}
	
	public Collection<T> getMultipleItem(String where_clause) throws BasedException {
		String _sql = "SELECT * FROM " + interactTableName;
		if (where_clause == null || where_clause.equals("")) {
			_sql += ";";
		} else {
			_sql += " WHERE " + where_clause + ";";
		}
		try {
			Statement st = mysqlConnector.getConnectionObject().createStatement();
			ResultSet rs = st.executeQuery(_sql);
			rs.beforeFirst();
			if (rs.next()) {
				Collection<T> result = convertResultSetToObjects(rs);
				rs.close();
				return result;
			}
			return new ArrayList<T>();
		} catch (Exception e) {
			throw new BasedException(ExceptionCause.DatabaseEntryAccessingFail, "sql = " + _sql, e);
		}
	}
	
	@Override
	public T get(String where_clause) throws BasedException {
		String _sql = "SELECT * FROM " + interactTableName + " WHERE " + where_clause + ";";
		try {
//			if (cache != null && isCacheEnabled()) {
//				T obj = cache.getItemFromCache(id);
//				if (obj != null) {
//					return obj;
//				}
//			}
			Statement st = mysqlConnector.getConnectionObject().createStatement();
			ResultSet rs = st.executeQuery(_sql);
			rs.beforeFirst();
			if (rs.next()) {
				rs.first();
				T result = convertResultSetToObject(rs);
				rs.close();
				return result;
			}
			return null;
		} catch (Exception e) {
			throw new BasedException(ExceptionCause.DatabaseEntryAccessingFail, "sql = " + _sql, e);
		}
	}
	
	@Override
	public long create(T obj) throws BasedException {
		try {
			PreparedStatement st = convertObjectToInsertQuery(obj);
			st.executeUpdate();
			ResultSet rs = st.getGeneratedKeys();
			rs.beforeFirst();
			long genereatedId = -13333;
			if (rs.next()) {
				genereatedId = rs.getLong(1);
			}
			rs.close();
			st.close();
			return genereatedId;
		} catch (SQLException e) {
			throw new BasedException(ExceptionCause.DatabaseEntryCreationFail, e);
		}
	}
	
	@Override
	public long[] create(Collection<T> obj) throws BasedException {
		try {
			PreparedStatement st = convertObjectToInsertQuery(obj);
			st.executeUpdate();
			ResultSet rs = st.getGeneratedKeys();
			List<Long> generatedIds = null;
			if (rs.next()) {
				rs.first();
				do {
					generatedIds.add(rs.getLong(1));
				} while (rs.next());
			}
			rs.close();
			st.close();
			if (generatedIds == null) {
				return null;
			}
			long[] lngs = new long[generatedIds.size()];
			int i=0;
			for (Long l : generatedIds) {
				lngs[i++] = l;
			}
			return lngs;
		} catch (SQLException e) {
			throw new BasedException(ExceptionCause.DatabaseEntryCreationFail, e);
		}
	}

	@Override
	public void update(T obj) throws BasedException {
		try {
//			if (cache != null && isCacheEnabled()) {
//				cache.removeItemFromCache(obj.hashCode());	// TODO Fix use more proper identification method.
//			}
			PreparedStatement st = convertObjectToUpdateQuery(obj);
			st.executeUpdate();
			st.close();
		} catch (SQLException e) {
			throw new BasedException(ExceptionCause.DatabaseEntryUpdateFail, e);
		}
	}
//	
//	public long[] search(String sql_filter_criteria_string, 
//			String sql_sorting_criteria_string, long limit_count, long limit_offset) throws BasedException {
//		try {
//			String sql = "SELECT id FROM " + interactTableName 
//				+ ((sql_filter_criteria_string == null || sql_filter_criteria_string.equals(""))?"":(" WHERE " + sql_filter_criteria_string))
//				+ ((sql_sorting_criteria_string == null || sql_sorting_criteria_string.equals(""))?"":(" ORDER BY " + sql_sorting_criteria_string))
//				+ ((limit_count <= 0)?"":" LIMIT " + ((limit_offset <= 0)?""+limit_count:""+limit_offset + "," + limit_count));
//			Statement st = mysqlConnector.getConnectionObject().createStatement();
//			ResultSet rs = st.executeQuery(sql);
//			long[] result = ripLongArrayFromResultSet(rs);
//			log.info("query: " + sql);
//			rs.close();
//			st.close();
//			return result;
//		} catch (SQLException e) {
//			throw new BasedException(ExceptionCause.DatabaseExecuteFail, e);
//		}
//	}
	
	private long[] ripLongArrayFromResultSet(ResultSet rs) throws SQLException {
		rs.last();
		int size = rs.getRow();
		long[] result = new long[size];
		int i=0;
		for(rs.beforeFirst();rs.next();i++) {
			result[i] = rs.getLong(1);
		}
		return result;
	}
	
	/* helpers */
	
	protected byte[] createByteArrayFromObject(Serializable o) throws IOException {
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		ObjectOutputStream oos;
		oos = new ObjectOutputStream(baos);
		oos.writeObject(o);
		oos.close();
		return baos.toByteArray();
	}
	
	protected Object createObjectFromByteArray(byte[] byteObject) throws IOException, ClassNotFoundException {
		ByteArrayInputStream bais;
		ObjectInputStream ois;
		bais = new ByteArrayInputStream(byteObject);
		ois = new ObjectInputStream(bais);
		return ois.readObject();
		
	}
}
